package com.hhit.collaborative_learning.learning;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;

import com.hhit.collaborative_learning.entity.Blog;
import com.hhit.collaborative_learning.entity.ExpertList;
import com.hhit.collaborative_learning.entity.User;
import com.hhit.collaborative_learning.utils.DBAccess;
import com.hhit.collaborative_learning.utils.Utils;

public class StoreData {
	public static ArrayList<Blog> twoDaysBlog = new ArrayList<Blog>();
	public static ArrayList<Blog> newestRecommendBlog = new ArrayList<Blog>();
	public static ExpertList expertList = null;
	public static ArrayList<User> newestExperts = new ArrayList<User>();
	public static ArrayList<Blog> primeBlog=new ArrayList<Blog>();
	
	// flag=1:这两天最新 flag=2:最新推荐
	public static void getBlog(int flag) {
		ArrayList<Blog> blogList = new ArrayList<Blog>();
		String sql = "";
		if (flag == 1) {
			Calendar now = Calendar.getInstance();
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String current = sdf.format(now.getTime());
			now.add(Calendar.DATE, -2);
			String twoDaysAgo = sdf.format(now.getTime());
			sql = "select blogName,blogId,userId " +
					"from blog " +
					"where str_to_date(time,'%Y-%m-%d %H:%i:%s')>='"+twoDaysAgo+"' and str_to_date(time,'%Y-%m-%d %H:%i:%s')<='"+current+"'and isDelete=0 and state=1 " +
					"order by time limit 0,20";
		} else if (flag == 2) {
			sql = "select blogName,blogId,userId " +
					"from blog " +
					"where isRecommend=1 and state=1 and isDelete=0 " +
					"order by time limit 0,20";
		}
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBAccess.getInstance().getConn();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Blog blog = new Blog();
				blog.blogName = rs.getString("blogName");
				blog.blogId = rs.getInt("blogId");
				blog.userId =rs.getInt("userId");
				blogList.add(blog);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBAccess.getInstance().close(conn, ps, rs);
		}
		if (flag == 1) {
			twoDaysBlog.clear();
			twoDaysBlog.addAll(blogList);
		} else if (flag == 2) {
			newestRecommendBlog.clear();
			newestRecommendBlog.addAll(blogList);
		}
	}

	public static ArrayList<User> getCategoryExperts(ResultSet rs) {
		ArrayList<User> experts = new ArrayList<User>();
		try {
			while (rs.next()) {
				User user = new User();
				user.userId = rs.getInt("userId");
				user.userNickName = rs.getString("userNickName");
				experts.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return experts;
	}

	public static void getExperts() {
		expertList = new ExpertList();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select user.userId,userNickName,sum(readNum) total " +
				"from blog_expert left join blog on blog_expert.userId=blog.userId and state=1 and isDelete=0 ,user " +
				"where blog_expert.category=? and blog_expert.userId=user.userId and applyState=0 " +
				"group by blog_expert.userId order by total desc limit 0,6;";
		try {
			conn = DBAccess.getInstance().getConn();
			ps = conn.prepareStatement(sql);
			ps.setObject(1, "mobile");
			rs = ps.executeQuery();
			expertList.mobileExperts = getCategoryExperts(rs);

			ps = conn.prepareStatement(sql);
			ps.setObject(1, "web");
			rs = ps.executeQuery();
			expertList.webExperts = getCategoryExperts(rs);

			ps = conn.prepareStatement(sql);
			ps.setObject(1, "enterprise");
			rs = ps.executeQuery();
			expertList.enterpriseExperts = getCategoryExperts(rs);

			ps = conn.prepareStatement(sql);
			ps.setObject(1, "code");
			rs = ps.executeQuery();
			expertList.codeExperts = getCategoryExperts(rs);

			ps = conn.prepareStatement(sql);
			ps.setObject(1, "www");
			rs = ps.executeQuery();
			expertList.wwwExperts = getCategoryExperts(rs);

			ps = conn.prepareStatement(sql);
			ps.setObject(1, "database");
			rs = ps.executeQuery();
			expertList.databaseExperts = getCategoryExperts(rs);

			ps = conn.prepareStatement(sql);
			ps.setObject(1, "system");
			rs = ps.executeQuery();
			expertList.systemExperts = getCategoryExperts(rs);

			ps = conn.prepareStatement(sql);
			ps.setObject(1, "cloud");
			rs = ps.executeQuery();
			expertList.cloudExperts = getCategoryExperts(rs);

			ps = conn.prepareStatement(sql);
			ps.setObject(1, "software");
			rs = ps.executeQuery();
			expertList.softwareExperts = getCategoryExperts(rs);

			ps = conn.prepareStatement(sql);
			ps.setObject(1, "other");
			rs = ps.executeQuery();
			expertList.otherExperts = getCategoryExperts(rs);

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBAccess.getInstance().close(conn, ps, rs);
		}
	}

	public static void getNewestExperts() {
		newestExperts.clear();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select blog_expert.userId,userNickName,userIcon,brief "
				+ "from blog_expert,user "
				+ "where user.userId=blog_expert.userId and applyState=0 "
				+ "order by time desc " + "limit 0,2";
		try {
			conn = DBAccess.getInstance().getConn();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.userBrief = rs.getString("brief");
				user.userId = rs.getInt("userId");
				user.userNickName = rs.getString("userNickName");
				user.userIcon = rs.getString("userIcon");
				newestExperts.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBAccess.getInstance().close(conn, ps, rs);
		}
	}
	
	public static void getPrimeBlog(){
		primeBlog.clear();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select blogId,blogName,userId from blog where isPrime=1 and state=1 and isDelete=0 order by time desc limit 0,6";
		try {
			conn=DBAccess.getInstance().getConn();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				Blog blog=new Blog();
				blog.blogId=rs.getInt("blogId");
				blog.blogName=rs.getString("blogName");
				blog.userId=rs.getInt("userId");
				primeBlog.add(blog);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			DBAccess.getInstance().close(conn, ps, rs);
		}
	}
	
	public static HashMap<String, Object> getUserRankList(int userId){
		HashMap<String, Object> map=new HashMap<String, Object>();
		ArrayList<Blog> readRank=new ArrayList<Blog>();
		ArrayList<Blog> commentRank=new ArrayList<Blog>();
		ArrayList<Blog> recommentList=new ArrayList<Blog>();
		String sql1="select blogId,blogName,userId,readNum from blog where userId="+userId+" and state=1 and isDelete=0 order by readNum desc limit 0,10";
		String sql2="select blogId,blogName,userId,commentNum from blog where userId="+userId+" and state=1 and isDelete=0 order by commentNum desc limit 0,10";
		String sql3="select blogId,userId,blogName from blog where userId="+userId+" and isRecommend=1 and state=1 and isDelete=0 order by time desc limit 0,10";
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			conn=DBAccess.getInstance().getConn();
			ps=conn.prepareStatement(sql1);
			rs=ps.executeQuery();
			while(rs.next()){
				Blog blog=new Blog();
				blog.blogId=rs.getInt("blogId");
				blog.blogName=rs.getString("blogName");
				blog.readNum=rs.getInt("readNum");
				blog.userId=rs.getInt("userId");
				readRank.add(blog);
			}
			map.put("readRank", readRank);
			
			ps=conn.prepareStatement(sql2);
			rs=ps.executeQuery();
			while(rs.next()){
				Blog blog=new Blog();
				blog.blogId=rs.getInt("blogId");
				blog.blogName=rs.getString("blogName");
				blog.commentNum=rs.getInt("commentNum");
				blog.userId=rs.getInt("userId");
				commentRank.add(blog);
			}
			map.put("commentRank", commentRank);
			
			ps=conn.prepareStatement(sql3);
			rs=ps.executeQuery();
			while(rs.next()){
				Blog blog=new Blog();
				blog.blogId=rs.getInt("blogId");
				blog.blogName=rs.getString("blogName");
				blog.userId=rs.getInt("userId");
				recommentList.add(blog);
			}
			map.put("recommentList", recommentList);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			DBAccess.getInstance().close(conn, ps, rs);
		}
		return map;
	}
	
	public static void main(String[] args) {
	}
}
